using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OracleClient;
using System.Collections.Generic;
using Pms.Utils;
using Biblioteca.Utils;
using Biblioteca.DataBase;
/*
 * Autor      : IT-Developer1
 * Data       : 02/04/2010 11:03:48
 * Descricao  : 
 * Alteracoes : 
 * 
 */

namespace SERIX
{
   public class CIndicadoresAcoes
   {
       
       private string IDT_IND ;
       private string IDT_PROG ;
       private string IDT_ACAO ;
       private string ROT_IND ;
       private string TPO_IND ;
       private string TPO_VINC ;
       private string TPO_TOT ;
       private string IDT_CLA_IND ;
       private string DSC_IND ;
       private string TPO_VINC_OPCAO ;
       private string IDT_IND_LIMITE ;

       private string DSC_CLA_IND;
       private string DSC_IND_LIM;


	   /// <sumary>
       /// Construtor da classe indicadores_acoes
       /// <sumary>
       public CIndicadoresAcoes()
       {
   
       }

	   /// <sumary>
       /// Construtor da classe Indicadores_acoes
       /// <sumary>
       public CIndicadoresAcoes( string IDT_IND, string IDT_PROG, string IDT_ACAO, string ROT_IND, string TPO_IND, string TPO_VINC, string TPO_TOT, string IDT_CLA_IND, string DSC_IND, string TPO_VINC_OPCAO, string IDT_IND_LIMITE )
       {
          this.IDT_IND = IDT_IND;
          this.IDT_PROG = IDT_PROG;
          this.IDT_ACAO = IDT_ACAO;
          this.ROT_IND = ROT_IND;
          this.TPO_IND = TPO_IND;
          this.TPO_VINC = TPO_VINC;
          this.TPO_TOT = TPO_TOT;
          this.IDT_CLA_IND = IDT_CLA_IND;
          this.DSC_IND = DSC_IND;
          this.TPO_VINC_OPCAO = TPO_VINC_OPCAO;
          this.IDT_IND_LIMITE = IDT_IND_LIMITE;
       }

       public CIndicadoresAcoes(string IDT_IND, string IDT_PROG, string IDT_ACAO, string ROT_IND, string TPO_IND, string TPO_VINC, string TPO_TOT, string IDT_CLA_IND, string DSC_IND, string TPO_VINC_OPCAO, string IDT_IND_LIMITE, string DSC_CLA_IND, string DSC_IND_LIM)
       {
           this.IDT_IND = IDT_IND;
           this.IDT_PROG = IDT_PROG;
           this.IDT_ACAO = IDT_ACAO;
           this.ROT_IND = ROT_IND;
           this.TPO_IND = TPO_IND;
           this.TPO_VINC = TPO_VINC;
           this.TPO_TOT = TPO_TOT;
           this.IDT_CLA_IND = IDT_CLA_IND;
           this.DSC_IND = DSC_IND;
           this.TPO_VINC_OPCAO = TPO_VINC_OPCAO;
           this.IDT_IND_LIMITE = IDT_IND_LIMITE;
           
           this.DSC_CLA_IND = DSC_CLA_IND;
           this.DSC_IND_LIM = DSC_IND_LIM;
       }

	   #region Propriedades
     	/// <sumary>
        /// Identificador do indicador
        /// <sumary>
        public string Idt_ind 
		{
		   get
		   {
		       return this.IDT_IND;
		   }
		   set
		   {
		       this.IDT_IND = value;
		   }
		}
     	/// <sumary>
        /// Identificador do programa
        /// <sumary>
        public string Idt_prog 
		{
		   get
		   {
		       return this.IDT_PROG;
		   }
		   set
		   {
		       this.IDT_PROG = value;
		   }
		}
     	/// <sumary>
        /// Identificador da acao
        /// <sumary>
        public string Idt_acao 
		{
		   get
		   {
		       return this.IDT_ACAO;
		   }
		   set
		   {
		       this.IDT_ACAO = value;
		   }
		}
     	/// <sumary>
        /// Rotulo do indicador
        /// <sumary>
        public string Rot_ind 
		{
		   get
		   {
		       return this.ROT_IND;
		   }
		   set
		   {
		       this.ROT_IND = value;
		   }
		}
     	/// <sumary>
        /// Tipo de indicador: N - Nao Planejado, P - Planejado
        /// <sumary>
        public string Tpo_ind 
		{
		   get
		   {
		       return this.TPO_IND;
		   }
		   set
		   {
		       this.TPO_IND = value;
		   }
		}
     	/// <sumary>
        /// Tipo de vinculo do indicador: P - Produtor, O - Organizacao
        /// <sumary>
        public string Tpo_vinc 
		{
		   get
		   {
		       return this.TPO_VINC;
		   }
		   set
		   {
		       this.TPO_VINC = value;
		   }
		}
     	/// <sumary>
        /// Tipo de totalizador: S - Soma, M - Media, A - Maior, E - Menor, N - Normal, R - Mais Recente
        /// <sumary>
        public string Tpo_tot 
		{
		   get
		   {
		       return this.TPO_TOT;
		   }
		   set
		   {
		       this.TPO_TOT = value;
		   }
		}
     	/// <sumary>
        /// Identificador da classe do indicador
        /// <sumary>
        public string Idt_cla_ind 
		{
		   get
		   {
		       return this.IDT_CLA_IND;
		   }
		   set
		   {
		       this.IDT_CLA_IND = value;
		   }
		}
     	/// <sumary>
        /// Descricao do indicador
        /// <sumary>
        public string Dsc_ind 
		{
		   get
		   {
		       return this.DSC_IND;
		   }
		   set
		   {
		       this.DSC_IND = value;
		   }
		}
     	/// <sumary>
        /// Tipo de v?nculo com a descri??o da op??o: C - Cultura, E - Esp?cie Animal, B - Bem de Consumo, S - Sem V?nculo
        /// <sumary>
        public string Tpo_vinc_opcao 
		{
		   get
		   {
		       return this.TPO_VINC_OPCAO;
		   }
		   set
		   {
		       this.TPO_VINC_OPCAO = value;
		   }
		}
     	/// <sumary>
        /// Identificador do indicador limite
        /// <sumary>
        public string Idt_ind_limite 
		{
		   get
		   {
		       return this.IDT_IND_LIMITE;
		   }
		   set
		   {
		       this.IDT_IND_LIMITE = value;
		   }
		}

        public string Dsc_cla_ind
        {
            get { return DSC_CLA_IND; }
            set { DSC_CLA_IND = value; }
        }

        public string Dsc_ind_lim
        {
            get { return DSC_IND_LIM; }
            set { DSC_IND_LIM = value; }
        }

	    #endregion Propriedades
	
	    /// <summary>
        /// Inserindo um novo Indicadores_acoes.
        /// </summary>
        public void Inserir(string Login)
        {
		    OracleCommand command = new OracleCommand();
            OracleTransaction trans;

            string SQL = @"insert into Serix.SRX_INDICADORES_ACOES
			                  ( IDT_IND, IDT_PROG, IDT_ACAO, ROT_IND, TPO_IND, TPO_VINC, TPO_TOT, IDT_CLA_IND, DSC_IND, TPO_VINC_OPCAO, IDT_IND_LIMITE )
						   values
						      ( :IDT_IND, :IDT_PROG, :IDT_ACAO, :ROT_IND, :TPO_IND, :TPO_VINC, :TPO_TOT, :IDT_CLA_IND, :DSC_IND, :TPO_VINC_OPCAO, :IDT_IND_LIMITE )";
     		
			try
			{	
				command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo);
                trans = command.Connection.BeginTransaction();
                command.Transaction = trans;
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
				
				command.Parameters.Add(new OracleParameter("IDT_IND",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("IDT_PROG",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("IDT_ACAO",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("ROT_IND",OracleType.Char)); 
				command.Parameters.Add(new OracleParameter("TPO_IND",OracleType.Char)); 
				command.Parameters.Add(new OracleParameter("TPO_VINC",OracleType.Char)); 
				command.Parameters.Add(new OracleParameter("TPO_TOT",OracleType.Char)); 
				command.Parameters.Add(new OracleParameter("IDT_CLA_IND",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("DSC_IND",OracleType.VarChar)); 
				command.Parameters.Add(new OracleParameter("TPO_VINC_OPCAO",OracleType.Char)); 
				command.Parameters.Add(new OracleParameter("IDT_IND_LIMITE",OracleType.Number)); 
				
                command.Parameters["IDT_IND"].Value = this.IDT_IND;
                command.Parameters["IDT_PROG"].Value = this.IDT_PROG;
                command.Parameters["IDT_ACAO"].Value = this.IDT_ACAO;
                command.Parameters["ROT_IND"].Value = this.ROT_IND;
                command.Parameters["TPO_IND"].Value = this.TPO_IND;
                command.Parameters["TPO_VINC"].Value = this.TPO_VINC;
                command.Parameters["TPO_TOT"].Value = this.TPO_TOT;
                command.Parameters["IDT_CLA_IND"].Value = this.IDT_CLA_IND;
                command.Parameters["DSC_IND"].Value = this.DSC_IND;
                command.Parameters["TPO_VINC_OPCAO"].Value = this.TPO_VINC_OPCAO;
                if (String.IsNullOrEmpty(this.IDT_IND_LIMITE))
                {
                    command.Parameters["IDT_IND_LIMITE"].Value = DBNull.Value;
                }
                else
                {
                    command.Parameters["IDT_IND_LIMITE"].Value = this.IDT_IND_LIMITE;
                }  
				command.ExecuteNonQuery();
                command.Parameters.Clear();
                command.Transaction.Commit();
            }
            catch (Exception e)
            {
                command.Transaction.Rollback();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
		}
		
	    /// <summary>
        /// Alterando um Indicadores_acoes.
        /// </summary>
        public void Alterar(string Login)
        {
		    OracleCommand command = new OracleCommand();
            OracleTransaction trans;

            string SQL = @"update Serix.SRX_INDICADORES_ACOES set 
			                   IDT_PROG = :IDT_PROG, IDT_ACAO = :IDT_ACAO, ROT_IND = :ROT_IND, TPO_IND = :TPO_IND, TPO_VINC = :TPO_VINC, TPO_TOT = :TPO_TOT, IDT_CLA_IND = :IDT_CLA_IND, DSC_IND = :DSC_IND, TPO_VINC_OPCAO = :TPO_VINC_OPCAO, IDT_IND_LIMITE = :IDT_IND_LIMITE
						   where
						       IDT_IND = :IDT_IND ";
     		
			try
			{	
				command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo);
                trans = command.Connection.BeginTransaction();
                command.Transaction = trans;
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
				
				command.Parameters.Add(new OracleParameter("IDT_IND",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("IDT_PROG",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("IDT_ACAO",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("ROT_IND",OracleType.Char)); 
				command.Parameters.Add(new OracleParameter("TPO_IND",OracleType.Char)); 
				command.Parameters.Add(new OracleParameter("TPO_VINC",OracleType.Char)); 
				command.Parameters.Add(new OracleParameter("TPO_TOT",OracleType.Char)); 
				command.Parameters.Add(new OracleParameter("IDT_CLA_IND",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("DSC_IND",OracleType.VarChar)); 
				command.Parameters.Add(new OracleParameter("TPO_VINC_OPCAO",OracleType.Char)); 
				command.Parameters.Add(new OracleParameter("IDT_IND_LIMITE",OracleType.Number)); 
				
                command.Parameters["IDT_IND"].Value = this.IDT_IND;
                command.Parameters["IDT_PROG"].Value = this.IDT_PROG;
                command.Parameters["IDT_ACAO"].Value = this.IDT_ACAO;
                command.Parameters["ROT_IND"].Value = this.ROT_IND;
                command.Parameters["TPO_IND"].Value = this.TPO_IND;
                command.Parameters["TPO_VINC"].Value = this.TPO_VINC;
                command.Parameters["TPO_TOT"].Value = this.TPO_TOT;
                command.Parameters["IDT_CLA_IND"].Value = this.IDT_CLA_IND;
                command.Parameters["DSC_IND"].Value = this.DSC_IND;
                command.Parameters["TPO_VINC_OPCAO"].Value = this.TPO_VINC_OPCAO;
                if (String.IsNullOrEmpty(this.IDT_IND_LIMITE))
                {
                    command.Parameters["IDT_IND_LIMITE"].Value = DBNull.Value;
                }
                else
                {
                    command.Parameters["IDT_IND_LIMITE"].Value = this.IDT_IND_LIMITE;
                }  
				command.ExecuteNonQuery();
                command.Parameters.Clear();
                command.Transaction.Commit();
            }
            catch (Exception e)
            {
                command.Transaction.Rollback();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
		}
		
	     /// <summary>
        /// Exclui um  Indicadores_acoes.
        /// </summary>
        public void Excluir(string Login)
        {
            Tabela tab = new Tabela("SRX_INDICADORES_ACOES", BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login,BiblioPMS.Demo));
            tab.adicionarFiltro(new Coluna("IDT_IND", DbType.Int32, this.IDT_IND));
            tab.delete();
        }
		
		static public DataTable ObterTodos()
        {
            string login = "serix";
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_INDICADORES_ACOES order by DSC_IND ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;

                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection)); 
                return tbl; 
                
            }
            catch (Exception e)
            {
 
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }
		
		static public CIndicadoresAcoes PesquisaporCodigo(string idt_ind, string login)
        {
            CIndicadoresAcoes obj;
            string SQL = @"SELECT IA.IDT_ACAO, IA.ROT_IND, IA.IDT_PROG, IA.IDT_IND, IA.IDT_IND_LIMITE, IA.TPO_VINC_OPCAO, IA.DSC_IND, IA.IDT_CLA_IND, IA.TPO_IND, IA.TPO_VINC, IA.TPO_TOT, CI.DSC_CLA_IND, IA2.DSC_IND AS DSC_IND_LIM 
                            FROM Serix.SRX_INDICADORES_ACOES IA 
                                 JOIN Serix.SRX_CLASSES_INDICADORES CI ON (IA.IDT_CLA_IND = CI.IDT_CLA_IND) 
                                 JOIN Serix.SRX_INDICADORES_ACOES IA2 ON (IA.IDT_IND = IA2.IDT_IND) 
                            WHERE IA.IDT_IND = :IDT_IND";
            Query qy = null;
            try
            {
                qy = new Query(BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, BiblioPMS.Demo), SQL);
				qy.AdicionarParametro(":IDT_IND", DbType.String, idt_ind);
               
				qy.Executar();
                obj = new CIndicadoresAcoes(qy.ObterCampo("IDT_IND"), qy.ObterCampo("IDT_PROG"), qy.ObterCampo("IDT_ACAO"), qy.ObterCampo("ROT_IND"), qy.ObterCampo("TPO_IND"), qy.ObterCampo("TPO_VINC"), qy.ObterCampo("TPO_TOT"), qy.ObterCampo("IDT_CLA_IND"), qy.ObterCampo("DSC_IND"), qy.ObterCampo("TPO_VINC_OPCAO"), qy.ObterCampo("IDT_IND_LIMITE"), qy.ObterCampo("DSC_CLA_IND"), qy.ObterCampo("DSC_IND_LIM"));
            }                                  
            catch
            {
                if (qy.EstadoConexao() == ConnectionState.Open)
                {
                    qy.FecharConexao();
                }
                return new CIndicadoresAcoes();
            }
			finally
            {
                qy.FecharConexao();
            }
            return obj;
        }

        static public DataTable ObterDescMestre(string idt_ind, string login)
        {
            if (string.IsNullOrEmpty(idt_ind))
                return new DataTable();
            OracleCommand command = new OracleCommand();
            string SQL = @"SELECT IA.DSC_IND 
                            FROM Serix.Srx_Indicadores IA 
where IA.IDT_IND = :P_IDT_IND";
            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
                command.Parameters.Add(new OracleParameter("P_IDT_IND", OracleType.Number));
                command.Parameters["P_IDT_IND"].Value = idt_ind;
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
            finally
            {
                if (command != null && command.Connection.State != ConnectionState.Closed)
                    command.Connection.Close();
            }
        }
        
       static public int ObterNovoIdt(string login)
        {
            string SQL = @"SELECT PKG_BIBSERIX.OBTER_IDT_IND() IDT From dual ";
            Query qy = null;
            try
            {
                qy = new Query(BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, BiblioPMS.Demo), SQL);
				qy.Executar();
				return Convert.ToInt32(qy.ObterCampo("IDT"));
            }
            catch
            {
                if (qy.EstadoConexao() == ConnectionState.Open)
                {
                    qy.FecharConexao();
                }
                return-1;
            }
			finally
            {
                qy.FecharConexao();
            }
        }
		
		static public DataTable ObterPorIDT_ACAO(string idt_acao)
        {
            string login = "serix";
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_INDICADORES_ACOES  where IDT_ACAO = :IDT_ACAO order by DSC_IND ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
                command.Parameters.Add(new OracleParameter("IDT_ACAO", OracleType.Number));
                command.Parameters["IDT_ACAO"].Value = idt_acao;
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }
		static public DataTable ObterPorIDT_CLA_IND(string idt_cla_ind)
        {
            string login = "serix";
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_INDICADORES_ACOES  where IDT_CLA_IND = :IDT_CLA_IND order by DSC_IND ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
                command.Parameters.Add(new OracleParameter("IDT_CLA_IND", OracleType.Number));
                command.Parameters["IDT_CLA_IND"].Value = idt_cla_ind;
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }
		static public DataTable ObterPorIDT_IND_LIMITE(string idt_ind_limite)
        {
            string login = "serix";
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_INDICADORES_ACOES  where IDT_IND_LIMITE = :IDT_IND_LIMITE order by DSC_IND ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
                command.Parameters.Add(new OracleParameter("IDT_IND_LIMITE", OracleType.Number));
                command.Parameters["IDT_IND_LIMITE"].Value = idt_ind_limite;
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }
        
    }
}	

